package net.xisberto.bustime.control;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Calendar;

import net.xisberto.bustime.model.Bus;
import net.xisberto.bustime.model.Record;
import net.xisberto.bustime.model.Region;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SQLiteResourceDatabase extends SQLiteOpenHelper {
	private static String DB_PATH = "/data/data/net.xisberto.bustime/databases/";
	private static String DB_NAME = "bustime.db";

	private SQLiteDatabase database;
	private Context context;

	/**
	 * Constructor
	 * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
	 * @param context
	 */
	public SQLiteResourceDatabase(Context context) {
		super(context, DB_NAME, null, 1);
		this.context = context;
	}	

	@Override
	public void onCreate(SQLiteDatabase db) {
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	/**
	 * Creates a empty database on the system and rewrites it with your own database.
	 */
	public void createDataBase() throws IOException{
		boolean dbExist = checkDataBase();

		if(dbExist){
			//do nothing - database already exist
		}else{
			//By calling this method and empty database will be created into the default system path
			//of your application so we are gonna be able to overwrite that database with our database.
			//database = context.openOrCreateDatabase(DB_NAME, context.MODE_PRIVATE, null);
			database = this.getReadableDatabase();
			copyDataBase();
		}
	}

	/**
	 * Check if the database already exist to avoid re-copying the file each time you open the application.
	 * @return true if it exists, false if it doesn't
	 */
	public boolean checkDataBase(){
		File dbFile = new File (DB_PATH + DB_NAME);
		return dbFile.exists();
	}

	/**
	 * Copies your database from your local assets-folder to the just created empty database in the
	 * system folder, from where it can be accessed and handled.
	 * This is done by transfering bytestream.
	 */
	private void copyDataBase() throws IOException{
		//Open your local db as the input stream
		InputStream myInput = context.getAssets().open(DB_NAME);
		// Path to the just created empty db
		String outFileName = DB_PATH + DB_NAME;
		//Open the empty db as the output stream
		OutputStream myOutput = new FileOutputStream(outFileName);
		//transfer bytes from the inputfile to the outputfile
		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer))>0){
			myOutput.write(buffer, 0, length);
		}
		//Close the streams
		myOutput.flush();
		myOutput.close();
		myInput.close();
	}

	public void openDataBase() throws SQLException{
		database = this.getReadableDatabase();
	}

	@Override
	public synchronized void close() {
		if(database != null)
			database.close();
		super.close();
	}
	
	public long insertIntoPersonal(int bus_id) {
		String[] test_columns = {"_id", "bus_id"};
		String[] test_args = {String.valueOf(bus_id)};
		Cursor test = database.query("personal", test_columns, "bus_id=?", test_args, null, null, null);
		if (test.getCount() > 0) {
			return -1;
		}
		ContentValues cv = new ContentValues(1);
		cv.put("bus_id", bus_id);
		return database.insert("personal", null, cv);
	}
	
	public void removeFromPersonal(int personal_id) {
		String[] args = {String.valueOf(personal_id)};
		database.delete("personal", "bus_id=?", args);
	}
	
	public ArrayList<Bus> listPersonal() {
		String[] columns = {"personal._id", "source._id", "number", "name", "region"};
		Cursor c = database.query("source, personal", columns, "source._id=personal.bus_id", null, null, null, "source.name");
		ArrayList<Bus> result = new ArrayList<Bus>();
		if (c.getCount() == 0) {
			return result;
		}
		c.moveToFirst();
		do {
			result.add(new Bus(c.getInt(1), c.getInt(2), c.getString(3), c.getInt(4)));
		} while (c.moveToNext());
		return result;
	}
	
	public long registerBus(long time, double latitude, double longitude, int bus_id) {
		ContentValues cv = new ContentValues(1);
		cv.put("source_id", bus_id);
		cv.put("time", time);
		cv.put("latitude", latitude);
		cv.put("longitude", longitude);
		return database.insert("record", null, cv);
	}
	
	public ArrayList<Record> listRecordsByBusName(String bus_name) {
		ArrayList<Record> result = new ArrayList<Record>();
		String[] columns = {"source._id", "source.name", "time", "latitude", "longitude"};
		String[] args = {bus_name};
		Cursor c = database.query("record, source", columns, "source._id=source_id and name like ?", args, null, null, "source_id");
		c.moveToFirst();
		if (c.getCount() < 1) {
			return result;
		}
		do {
			result.add(new Record(c.getString(1), c.getLong(2), c.getDouble(3), c.getDouble(4)));
		} while (c.moveToNext());
		return result;
	}
	
	/**Tries to select records from the database by comparing the parameter <strong>time</strong> with the record times in database.
	 * @param time
	 * @return
	 */
	public ArrayList<Record> listRecordsByTime(long time) {
		ArrayList<Record> result = new ArrayList<Record>();
		//TODO finish this
		String[] columns = {"source_id", "source.name", "time", "latitude", "longitude"};
		String[] args = {String.valueOf(time)};
		Cursor c = database.query("record, source", columns, "source._id=source_id and time>?", args, null, null, "name");
		c.moveToFirst();
		if (c.getCount() < 1) {
			return result;
		}
		do {
			result.add(new Record(c.getString(1), c.getLong(2), c.getDouble(3), c.getDouble(4)));
		} while (c.moveToNext());
		return result;
	}
	
	public ArrayList<String> listRecordBuses() {
		ArrayList<String> result = new ArrayList<String>();
		String[] columns = {"source.name"};
		Cursor c = database.query("record, source", columns, "source._id=source_id", null, "source.name", null, "source.name");
		c.moveToFirst();
		if (c.getCount() < 1) {
			return result;
		}
		do {
			result.add(c.getString(0));
		} while (c.moveToNext());
		return result;
	}
	
	/**List the times (not dates) gathered from record table in database.
	 * @return
	 */
	public ArrayList<Long> listRecordTimes() {
		ArrayList<Long> result = new ArrayList<Long>();
		String[] columns = {"record.time"};
		Cursor c = database.query("record, source", columns, "source._id=source_id", null, null, null, "record.time");
		c.moveToFirst();
		if (c.getCount() <1 ) {
			return result;
		}
		do {
			result.add(c.getLong(0));
		} while (c.moveToNext());
		return result;
	}
	
	public ArrayList<Bus> listSource(String name, int region) {
		ArrayList<Bus> result = new ArrayList<Bus>();
		String[] columns = {"_id", "number", "name", "region"};
		String[] args = new String[] {"%"+name+"%", String.valueOf(region)};
		Cursor c = database.query("source", columns, "name like ? and region=?", args, null, null, "name");
		c.moveToFirst();
		if (c.getCount() <= 0) {
			return result;
		}
		do {
			result.add(new Bus(c.getInt(0), c.getInt(1), c.getString(2), c.getInt(3)));
		} while (c.moveToNext());
		return result;
	}
	
	public Bus getBus(int id) {
		Bus result = null;
		String[] columns = {"_id", "number", "name", "region"};
		String[] args = new String[] {String.valueOf(id)};
		Cursor c = database.query("source", columns, "_id=?", args, null, null, null);
		c.moveToFirst();
		Log.i(getClass().getCanonicalName(), String.valueOf(c.getCount()));
		if (c.getCount() < 0) {
			return result;
		}
		result = new Bus(c.getInt(0), c.getInt(1), c.getString(2), c.getInt(3));
		return result;
	}
	
	public ArrayList<Region> listRegion(String name) {
		ArrayList<Region> result = new ArrayList<Region>();
		String[] columns = {"_id", "name"};
		String[] args = new String[] {"%"+name+"%"};
		Cursor c = database.query("region", columns, "name like ?", args, null, null, "name");
		c.moveToFirst();
		do {
			result.add(new Region(c.getInt(0), c.getString(1)));
		} while (c.moveToNext());
		return result;
	}

}

